﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using System.Reflection;

namespace InsideDL
{
    public partial class frmInside : MISControl.frmMatrix //Form
    {
        
        //protected string[] downList = null;
        protected string saveFilePath
        {
            get
            {
                return txtFtpFilePath.Text;
            }
        }
        protected Dictionary<string, object> sheetSource = null;
        protected string[] sheetNames =
        {"I002均額", "IOBU海外均額", "I002損益", "IOBU海外損益"};
        protected ExcelSheet excelSheet = null;
        protected Microsoft.Office.Interop.Excel.Application exlAPP = null;
        protected string lastExeceFile = string.Empty;
        protected MISData.DataInside di = null;
        protected string[] refreshSheets = { "公庫存款利息","OBU海外損益"
                                            ,"損益C","002損益","公庫存款均額"
                                            ,"均額C","OBU海外均額","002均額" };
        public frmInside()
        {
            InitializeComponent();
            init();
            ucExecute1.ExecuteClick += new EventHandler(ucClickFacade);
            ucExecute_Exit.ExecuteClick += new EventHandler(ucClickFacade);
            
        }

    
        protected void init()
        {
            //tempFolderExists();
            Utility.LoadConfigValue("FTPCollection");
            SetProperty();
            excelSheet = new ExcelSheet();            
        }

        /// <summary>
        /// 在這設定相關變數值
        /// </summary>
        protected void SetProperty()
        {
            ftp = Utility.GetConfigValue("ftp");
            id = Utility.GetConfigValue("ftpid");
            pw = Utility.GetConfigValue("ftppw");
            currentYM = Utility.GetCurrentYM();
        }
      
       
        private void button6_Click(object sender, EventArgs e)
        {
            //L_all
            //System.Data.DataTable dt = Utility.GetMargin("L_all", @"D:\MY\WORK\非非\Transfer Margin_20101130.XLS");
            //System.Data.DataTable dt = Utility.GetEIS("DAT", @"D:\MY\WORK\非非\EIS_Results_02_CUR.XLS");
            //string aa = Utility.GetCurrentYM() + "01";
            //DateTime dd = Convert.ToDateTime(aa);
            string ddd = Utility.BeforeMonthLastDay(Utility.GetCurrentYM());
        }
      

        protected void ShowStatus(string p_msg)
        {
            showStatus(p_msg, lblStatus);
        }

        protected override void execute()
        {
            exlAPP = new Microsoft.Office.Interop.Excel.Application();
            if (!verify()) return;
            //step1 copy file from ftp site               
            if (!CheckFileInFTPSite())
            {
                showMessage(this.Text, "沒有本月檔案！！");
                return;
            }
            ShowStatus("Down Load FTP 檔案!");
            if (!DownloadFileFromFtp())
            {
                showMessage(this.Text, "Download 失敗！！");
                return;
            }
            di = new InsideDL.MISData.DataInside();
            CreateCSVSChema();
            //step 2 connect csv file
            ShowStatus("取得CSV 資料來源!");
            getDataTables();
            ShowStatus("產生本月對內檔案!");
            createNewInside();
            OpenInsideFile();
            GetWorkSheets();
            ShowStatus("填入002 OBU 資料!");
            ClearAndFill();
            GetWorkSheets();
            ShowStatus("搬移本月資料!");
            moveLastValue();
            setThisMonthDay();
            refreshPivotTables();
            saveCloseXLS();
            exlAPP.Quit();
            ShowStatus("");
            showMessage(this.Text, "完成！！");
            if (exlAPP != null)
            {
                releaseObject(exlAPP);
            }
        }

        void refreshPivotTables()
        {
            foreach (string str in refreshSheets)
            {
                Microsoft.Office.Interop.Excel.Worksheet wsht = GetSheetObject(str) as
                    Microsoft.Office.Interop.Excel.Worksheet;
                Microsoft.Office.Interop.Excel.PivotTable pt = wsht.PivotTables(1)
                   as Microsoft.Office.Interop.Excel.PivotTable;
                            pt.PivotCache().Refresh();
            }           
        }

        void saveCloseXLS()
        {
            exlAPP.AlertBeforeOverwriting = false;
            exlAPP.DisplayAlerts = false;
            for (int i = 1; i <= exlAPP.Workbooks.Count; i++)
            {
                exlAPP.Workbooks[i].Save();
            }
        }

   
        protected void setThisMonthDay()
        {
            string[] months = Utility.GetThisLastMonth(downList[0],"");
            string[] days = Utility.GetThisLastDay(downList[0]);
            string strSheet = "3全行";
            Microsoft.Office.Interop.Excel.Worksheet wsht = GetSheetObject(strSheet) as
                    Microsoft.Office.Interop.Excel.Worksheet;
            Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range("A1", Type.Missing);
            rng.Value2 = days[0];
            rng = wsht.get_Range("A4", Type.Missing);
            rng.Value2 = days[1];

            rng = wsht.get_Range("F5", Type.Missing);
            rng.Value2 = string.Format("'{0}", months[0]);
            rng = wsht.get_Range("I5", Type.Missing);
            rng.Value2 = string.Format("'{0}", months[1]);
        }

        /// <summary>
        /// 清除四個主要的sheet，並把資料填入
        /// </summary>
        protected void ClearAndFill()
        {
            //DataTable dt = null;
            ADODB.RecordsetClass dt = null;
            foreach (string sht in sheetNames)
            {
                Microsoft.Office.Interop.Excel.Worksheet wsht = GetSheetObject(sht) as
                    Microsoft.Office.Interop.Excel.Worksheet;
                Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range("A2:G10000", Type.Missing);
                rng.ClearContents();
                rng = wsht.get_Range("A2:F10000", Type.Missing);
                rng.NumberFormatLocal = "@";
                //dt = SheetSource.Tables[sht];
                dt = sheetSource[sht] as ADODB.RecordsetClass;
                int iRow = 2;
                rng = wsht.Cells[iRow, 1] as Microsoft.Office.Interop.Excel.Range;
                rng.CopyFromRecordset(dt,Type.Missing,Type.Missing);

            }
        }

        /// <summary>
        /// 打開要工作的EXCEL 表格
        /// </summary>
        protected void OpenInsideFile()
        {

            object oMissing = System.Reflection.Missing.Value;
            string insideXLS = lastExeceFile; //要開最後一個產生出來的EXCEL txtInsideDL.Text;
            //EXLApp = new Microsoft.Office.Interop.Excel.Application();
            exlAPP.Workbooks.Open(insideXLS, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing
                , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            if (button1.Visible == true)
            {
                exlAPP.Visible = true;
            }
            
        }

        //protected void ExcelOpenFile(string p_file)
        //{
        //    object oMissing = System.Reflection.Missing.Value;
        //    string insideXLS = lastExeceFile; //要開最後一個產生出來的EXCEL txtInsideDL.Text;
        //    //EXLApp = new Microsoft.Office.Interop.Excel.Application();
        //    exlAPP.Workbooks.Open(p_file, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing
        //        , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);        
        //}


        /// <summary>
        /// 找出所有要搬資料的sheets
        /// </summary>
        protected void GetWorkSheets()
        {            
            string xlsConnection = string.Format(
                @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\ExcelSheets.xls ;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
                , AppDomain.CurrentDomain.BaseDirectory);
            DataTable sheetsDT = Utility.GetSheets(xlsConnection);
            foreach (DataRow row in sheetsDT.Rows)
            {
                excelSheet.AddSheet(row);
            }        
        }
        
        /// <summary>
        /// 把特定的sheet 當月的值搬到上個月去放
        /// </summary>
        protected void moveLastValue()
        {
            Microsoft.Office.Interop.Excel.Worksheet wsht = null;
            Microsoft.Office.Interop.Excel.Range sourceRng, targetRng;
            sourceRng = null;
            targetRng = null;
            string rangeString, rangeString2;
            int xx = 0;
            foreach(Sheet sht in excelSheet.Sheets)
            {
                ShowStatus(string.Format("搬移{0}資料!",sht.SheetName));
             
                wsht = GetSheetObject(sht.SheetName) as
                    Microsoft.Office.Interop.Excel.Worksheet;
                if (wsht == null)
                {
                    throw new Exception(
                            string.Format("moveLastValue ERROR MSG:{0} 工作表不存在",
                            sht.SheetName));
                }
                foreach (Section sct in sht.Sections)
                {
                    rangeString = sht.GetThisMonthArea(sct);
                    sourceRng = wsht.get_Range(rangeString, Type.Missing);
                    sourceRng.Copy(Type.Missing);
                    rangeString2 = sht.GetLastMonthArea(sct);
                    targetRng = wsht.get_Range(rangeString2, Type.Missing);
                    targetRng.PasteSpecial(
                        Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues
                        , Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone
                        ,false
                        ,false);
                    
                    if ((!sct.IsCangeCaption()) & (xx.Equals(0))) //只要改第一次就好了...
                    {
                        xx = 1;
                        ChangeCaption(wsht, sht, sct.GetCaptionRow().ToString());
                    }                    
                }
                xx = 0;
            }
        }

        /// <summary>
        /// 更改上月份的標題，要替換成本月份
        /// </summary>
        /// <param name="p_sht"></param>
        /// <param name="p_sheet"></param>
        protected void ChangeCaption(Microsoft.Office.Interop.Excel.Worksheet p_sht
            , Sheet p_sheet,string p_monthPos)
        {
            Microsoft.Office.Interop.Excel.Range sourceRng, targetRng;
            int i = 0;
            string monthChar;
            monthChar = p_sheet.StartColumn;
            sourceRng = p_sht.get_Range(monthChar + p_monthPos, Type.Missing);
            while (sourceRng.Value2 == null)
            {
                i++;
                monthChar = p_sheet.GetNewChar(p_sheet.StartColumn, i);
                sourceRng = p_sht.get_Range(monthChar + p_monthPos, Type.Missing);
            }
            targetRng = p_sht.get_Range(p_sheet.GetNewChar(p_sheet.EndColumn, i) + p_monthPos, Type.Missing);
            targetRng.Value2 = sourceRng.Value2;
        }

        /// <summary>
        /// 取回在EXCEL中所指定的Sheet
        /// </summary>
        /// <param name="p_sheetName"></param>
        /// <returns></returns>
        protected object GetSheetObject(string p_sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            for (int i = 1; i <= exlAPP.Sheets.Count; i++)
            {
                ws = (Microsoft.Office.Interop.Excel.Worksheet) exlAPP.Worksheets.get_Item(i);
                if (ws.Name.Trim().Equals(p_sheetName))
                {
                    break;
                }
            }
            return ws;
        }

        /// <summary>
        /// 在這產生出要用的對內檔案
        /// </summary>
        protected void createNewInside()
        {
            //也許可以用Path.Combine
            string target = string.Format(@"{1}\對內-{0}版塊別存放款.xls"
                ,Utility.GetTWYM()
                , System.IO.Path.GetDirectoryName(txtInsideDL.Text)
                );
            File.Copy(txtInsideDL.Text, target,true);
            lastExeceFile = target;
           
        }

        /// <summary>
        /// 找出CSV中的資料
        /// </summary>
        protected void getDataTables()
        {
            sheetSource = new Dictionary<string, object>();
            object dt;
            di = new InsideDL.MISData.DataInside();

            dt = di.GetI002BALRS(new string[] { downList[1], downList[2] }
            , saveFilePath,Utility.GetThisLastMonth(downList[0]));
            
            sheetSource.Add("I002均額", dt);

            dt = di.GetI002ProfitRS(downList[0], saveFilePath
                , Utility.GetThisLastMonth(downList[0]));
            sheetSource.Add("I002損益", dt);


            dt = di.GetOBUBALRS(new string[] { downList[1], downList[2] }
                , saveFilePath, Utility.GetThisLastMonth(downList[0]));


            sheetSource.Add("IOBU海外均額",dt);

            dt = di.GetOBUProfitRS(downList[0], saveFilePath
                , Utility.GetThisLastMonth(downList[0]));

            sheetSource.Add("IOBU海外損益", dt);

        }
        protected bool verify()
        {
            bool ok = true;
            if (txtFtpFilePath.Text.Length.Equals(0))
            {
                showMessage(this.Text, "請選擇存檔路徑！");
                txtFtpFilePath.Focus();
                return false;
            }

            if (txtInsideDL.Text.Length.Equals(0))
            {
                showMessage(this.Text, "請選擇對內存款檔案位置！");
                txtInsideDL.Focus();
                return false;
            }
            return ok;
        }
        
        protected bool DownloadFileFromFtp()
        {
            return Utility.DownloadFile(ftp, id, pw, downList, saveFilePath);            
        }

        /// <summary>
        /// 產生出CSV要用的 schema.ini
        /// </summary>
        protected void CreateCSVSChema()
        {
            Program.CreateFile(saveFilePath + "\\schema.ini", 
                Utility.GetARTMACDSchema(downList, di.GetCSVFields(downList,
                saveFilePath)));                
        }

        /// <summary>
        /// 要確認是否已有存在ARTM95AYYYYMMDDA、ARTM95AYYYYMMDDC、ARTM95AYYYYMMDDD這三個檔案
        /// </summary>
        /// <returns></returns>
        protected bool CheckFileInFTPSite()
        {
            
            string[] fileList = Utility.GetFTPFileList(ftp, id, pw);            
            downList = (from aa in
                                   (from fl in fileList
                                    where fl.IndexOf(".CSV") > 0
                                    orderby fl.Substring(8) descending
                                    select fl)
                               where aa.Substring(8, 6).Equals(currentYM) &
                                    (aa.Substring(6, 1).Equals("A")
                                        || aa.Substring(6, 1).Equals("C")
                                        || aa.Substring(6, 1).Equals("D"))
                               select aa).ToArray();
            return downList.Length == 3 ? true : false;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            selectPath(txtFtpFilePath);
        }

        protected void selectPath(System.Windows.Forms.Control p_control)
        { 
            FolderBrowserDialog fb = new FolderBrowserDialog();
            if (fb.ShowDialog()==DialogResult.OK)
            {
                p_control.Text = fb.SelectedPath;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Button btn = sender as Button;
            OpenFileDialog open = new OpenFileDialog();
            if (open.ShowDialog() == DialogResult.OK)
            {
                txtInsideDL.Text = open.FileName;
            }
        }

        
        /// <summary>
        /// 檔案順序為 A、C、D
        /// </summary>
        /// <param name="p_fileName"></param>
        /// <returns></returns>
//        protected string GetCSVSchema(string[] p_fileName)
//        {
//            //string[] flds = Utility.GetCSVFields(downList, saveFilePath);
//            string[] flds = di.GetCSVFields(downList, saveFilePath);
//            string[] ca = flds[0].ToString().Split(new char[] { ',' });
//            string[] cc = flds[1].ToString().Split(new char[] { ',' });
//            string[] cd = flds[2].ToString().Split(new char[] { ',' });
//            return string.Format(
//@"[{0}]
//CharacterSet=ANSI
//ColNameHeader=True
//Format=CSVDelimited
//Col1=年度 Text
//Col2=分行別 Text
//Col3=幣別 Text 
//Col4=大版塊 Text
//Col5=小版塊 Text
//Col6=科目 Text
//Col7=""{3}"" Double
//Col8=""{4}"" Double
//
//[{1}]
//CharacterSet=ANSI
//ColNameHeader=True
//Format=CSVDelimited
//Col1=年度 Text
//Col2=分行別 Text
//Col3=幣別 Text 
//Col4=大版塊 Text
//Col5=小版塊 Text
//Col6=科目 Text
//Col7=""{5}"" Double
//Col8=""{6}"" Double
//Col9=""{7}"" Double
//
//[{2}]
//CharacterSet=ANSI
//ColNameHeader=True
//Format=CSVDelimited
//Col1=年度 Text
//Col2=分行別 Text
//Col3=幣別 Text 
//Col4=大版塊 Text
//Col5=小版塊 Text
//Col6=科目 Text
//Col7=""{8}"" Double
//Col8=""{9}"" Double
//Col9=""{10}"" Double
//", p_fileName[0], p_fileName[1], p_fileName[2]
//,   ca[0],ca[1],cc[0],cc[1],cc[2],cd[0],cd[1],cd[2] 
// );
//        }

        private void txtFtpFilePath_DoubleClick(object sender, EventArgs e)
        {
            dateTimePicker1.Visible = dateTimePicker1.Visible == true ? false : true;
            dateTimePicker1.Refresh();
        }

        private void dateTimePicker1_Leave(object sender, EventArgs e)
        {
            dateTimePicker1.Visible = dateTimePicker1.Visible == true ? false : true;
            currentYM = Utility.GetCurrentYM(dateTimePicker1.Value);   
        }

        private void txtInsideDL_DoubleClick(object sender, EventArgs e)
        {
            button1.Visible = button1.Visible == true ? false : true;
        }  


    }
}
